﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Products : System.Web.UI.Page
{
    SqlConnection con;
    SqlCommand cmd;
    SqlParameter param;
    SqlDataAdapter da;
    DataView dv;
    DataTable dt;
    protected void Page_Load(object sender, EventArgs e)
    {
        initialconnect();
        if (!IsPostBack)
        {
            initialform();
            LoadProductsDisconnect();
            getDataSource_Supplier();
            getDataSource_Category();
            Session["isSelect"] = false;
        }
        bool tmp = (bool)Session["isSelect"];
        if (tmp)
        {
            btnDelete.Enabled = true;
            btnUpdate.Enabled = true;
            btnAdd.Enabled = false;
        }
        else
        {
            btnDelete.Enabled = false;
            btnUpdate.Enabled = false;
            btnAdd.Enabled = true;
        }
    }

    #region Code
    void initialconnect()
    {
        try
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        }
        catch (Exception)
        {
           ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Connect To Server Fail');", true);
        }
    }
    void initialform()
    {
        try
        {
            if (IsPostBack)
            {
                btnAdd.Enabled = true;
                btnUpdate.Enabled = false;
                btnDelete.Enabled = false;
            }
            txtProductID.Enabled = false;
            txtProductID.Text = "";
            txtProductName.Text = "";
            ddlSupplier.SelectedIndex = 0;
            ddlCategory.SelectedIndex = 0;
            txtUnitPrice.Text = "";
            txtSearchName.Text = "";
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Fails Page! F5 to Reload" + "')</script>");
        }
    }
    void LoadProductsDisconnect()
    {
        try
        {
            cmd = new SqlCommand();
            initialconnect();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM Production.Products";
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            gvProducts.AutoGenerateSelectButton = true;
            gvProducts.AllowPaging = true;
            gvProducts.PageSize = 10;
            gvProducts.DataSource = dt;
            gvProducts.DataBind();
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Load Data Fail!" + "')</script>");
        }
    }
    void AddProducts()
    {
        initialconnect();
        try
        {
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_Products_Insert";

            SqlParameter param = new SqlParameter("@productname", SqlDbType.NVarChar);
            param.Size = 40;
            param.Value = txtProductName.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@supplierid", SqlDbType.Int);
            param.Value = ddlSupplier.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@categoryid", SqlDbType.Int);
            param.Value = ddlCategory.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@unitprice", SqlDbType.Money);
            param.Value = txtUnitPrice.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@discontinued", SqlDbType.Bit);
            param.Value = rdDiscontinued.Checked;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            con.Close();
        }
    }
    void UpdateProducts()
    {
        initialconnect();
        try
        {
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_Products_Update";

            SqlParameter param = new SqlParameter("@productid", SqlDbType.Int);
            param.Value = txtProductID.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@productname", SqlDbType.NVarChar);
            param.Size = 40;
            param.Value = txtProductName.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@supplierid", SqlDbType.Int);
            param.Value = ddlSupplier.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@categoryid", SqlDbType.Int);
            param.Value = ddlCategory.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@unitprice", SqlDbType.Money);
            param.Value = txtUnitPrice.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@discontinued", SqlDbType.Bit);
            param.Value = rdDiscontinued.Checked;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            con.Close();
        }
    }
    void DeleteProducts()
    {
        initialconnect();
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_Products_Delete";

            SqlParameter param = new SqlParameter("@productid", SqlDbType.Int);
            param.Value = txtProductID.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
        }
    }
    void SearchProductName()
    {
        try
        {
            initialconnect();
            LoadProductsDisconnect();
            dv = new DataView(dt);
            dv.RowFilter = string.Format("productname LIKE'%{0}%'", txtSearchName.Text);
            gvProducts.DataSource = dv;
            gvProducts.DataBind();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    void SearchSupplier()
    {
        bool check = true;
        try
        {
            int.Parse(txtSearchName.Text.Trim());
        }
        catch 
        {
            check = false;
        }
        if (check)
            try
            {
                cmd = new SqlCommand();
                initialconnect();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("SELECT * FROM Production.Products WHERE supplierid= '{0}'", txtSearchName.Text);
                da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                gvProducts.DataSource = dt;
                gvProducts.DataBind();
            }
            catch (Exception)
            {
            }
        else
        {
            gvProducts.DataSource = null;
            gvProducts.DataBind();
        }
    }
    void SearchCategory()
    {
        bool check = true;
        try
        {
            int.Parse(txtSearchName.Text.Trim());
        }
        catch
        {
            check = false;
        }
        if (check)
            try
            {
                cmd = new SqlCommand();
                initialconnect();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("SELECT * FROM Production.Products WHERE categoryid= '{0}'", txtSearchName.Text);
                da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                gvProducts.DataSource = dt;
                gvProducts.DataBind();
            }
            catch (Exception)
            {
            }
        else
        {
            gvProducts.DataSource = null;
            gvProducts.DataBind();
        }
    }
    void getDataSource_Supplier()
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT supplierid,companyname FROM Production.Suppliers";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source, "Production.Suppliers");
            da.Fill(ds, "Production.Suppliers");
            DataTable dt = ds.Tables["Production.Suppliers"];
            da.Fill(dt);
            ddlSupplier.DataSource = dt;
            ddlSupplier.DataTextField = "companyname";
            ddlSupplier.DataValueField = "supplierid";
            ddlSupplier.DataBind();
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }
    void getDataSource_Category()
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT categoryid,categoryname FROM Production.Categories";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source, "Production.Categories");
            da.Fill(ds, "Production.Categories");
            DataTable dt = ds.Tables["Production.Categories"];
            da.Fill(dt);
            ddlCategory.DataSource = dt;
            ddlCategory.DataTextField = "categoryname";
            ddlCategory.DataValueField = "categoryid";
            ddlCategory.DataBind();
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }

    #endregion

    #region Event
    protected void gvProducts_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (gvProducts.Rows.Count > 0)
        {
            try
            {
                if (IsPostBack)
                {
                    btnAdd.Enabled = false;
                    btnDelete.Enabled = true;
                    btnUpdate.Enabled = true; 
                }
                GridViewRow r = gvProducts.SelectedRow;
                txtProductID.Text = r.Cells[1].Text.ToString();
                txtProductName.Text = Server.HtmlDecode(r.Cells[2].Text.ToString());
                ddlSupplier.Text = Server.HtmlDecode(r.Cells[3].Text.ToString());
                ddlCategory.Text = Server.HtmlDecode(r.Cells[4].Text.ToString());
                txtUnitPrice.Text = Server.HtmlDecode(r.Cells[5].Text.ToString());
                Session["isSelected"] = true;
            }
            catch (Exception)
            {
                Response.Write("<script>alert('" + "Fails Page! F5 to Reload" + "')</script>");
            }
        }
    }
    protected void gvProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            if (!(bool)Session["isSearch"])
            {
                gvProducts.PageIndex = e.NewPageIndex;
                LoadProductsDisconnect();
            }
            else
            {
                gvProducts.PageIndex = e.NewPageIndex;
                btnSearch_Click(sender, e);
            }
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Fails Page! F5 to Reload" + "')</script>");
        }
    }
    protected void btnNew_Click(object sender, EventArgs e)
    {
        initialform();
    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        bool check = true;
        try
        {
            AddProducts();
        }
        catch
        {
            check = false;
            Response.Write("<script>alert('" + "Add Fail!" + "')</script>");
        }
        finally
        {
            initialform();
        }
        if (check)
        {
            Response.Write("<script>alert('" + "Add Successfull!" + "')</script>");
            LoadProductsDisconnect();
        }
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        bool check = true;
        if (gvProducts.Rows.Count > 0)
        {
            try
            {
                UpdateProducts();
                initialform();
            }
            catch (Exception)
            {
                check = false;
                Response.Write("<script>alert('" + "Update Fail!" + "')</script>");
            }
            finally
            {
                initialform();
            }
            if (check)
            {
                Response.Write("<script>alert('" + "Update Success !" + "')</script>");
                LoadProductsDisconnect();
            }
        }
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        bool check = true;
        if (gvProducts.Rows.Count > 0)
        {
            try
            {
                DeleteProducts();
            }
            catch (Exception)
            {
                check = false;
                Response.Write("<script>alert('" + "Delete Fail!" + "')</script>");
            }
            finally
            {
                initialform();
            }
            if (check)
            {
                Response.Write("<script>alert('" + "Delete Successfull!" + "')</script>");
                LoadProductsDisconnect();
            }
        }
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        bool check = true;
        try
        {
            int tmp = cbSearch.SelectedIndex;
            if (tmp == 0)
                SearchProductName();
            if (tmp == 1)
                SearchSupplier();
            if (tmp == 2)
                SearchCategory();
            Session["isSearch"] = true;
        }
        catch (Exception)
        {
            check = false;
            Response.Write("<script>alert('" + "Search Fail" + "')</script>");
        }
        if (!check || txtSearchName.Text.Trim() == "")
        {
            LoadProductsDisconnect();
        }
        else
        {
            btnReload.Enabled = true;
        }
    }
    protected void btnReload_Click(object sender, EventArgs e)
    {
        LoadProductsDisconnect();
    }
    #endregion  
}
